﻿Imports Microsoft.VisualBasic
Imports System.Diagnostics
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports KurzCNB


Public Class PHE
    Public Function nactiUlozenyPoplatek(ByVal PHE As String) As Single
        Dim poplatek As Single
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("DataHorejsi").ConnectionString
        Dim dbConnection As System.Data.SqlClient.SqlConnection = New SqlConnection(connectionString)
        Dim myCommand As SqlCommand = New SqlCommand
        Dim mydataSet As New DataSet
        Dim dataAdapter As SqlDataAdapter
        Dim myRow As DataRow


        dbConnection.Open()
        myCommand.Connection = dbConnection


        myCommand.CommandText = String.Format("SELECT T_Parametry.Nazev as Mena, T_Parametry.Popis, T_ParametryValue.real_value as poplatek FROM T_ParametrTyp INNER JOIN" _
                      & " T_Parametry ON T_ParametrTyp.ID_typ = T_Parametry.ID_typ INNER JOIN" _
                      & " T_ParametryValue ON T_ParametrTyp.ID_typ = T_ParametryValue.ID_typ AND T_Parametry.ID_parametr = T_ParametryValue.ID_parametr" _
                      & " WHERE (T_Parametry.Nazev = '{0}')", PHE)
        myCommand.CommandType = CommandType.Text

        dataAdapter = New SqlDataAdapter(myCommand.CommandText, dbConnection)

        dataAdapter.Fill(mydataSet, "Poplatky")


        myRow = mydataSet.Tables("Poplatky").Rows(0)


        poplatek = myRow("poplatek")

        dbConnection.Close()

        Return poplatek
    End Function
    Public Function ulozPoplatek(ByVal PHE As String, ByVal hodnota As Single) As Integer
        Dim retVal As Integer
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("DataHorejsi").ConnectionString
        Dim dbConnection As System.Data.SqlClient.SqlConnection = New SqlConnection(connectionString)
        Dim myCommand As SqlCommand = New SqlCommand


        dbConnection.Open()
        myCommand.Connection = dbConnection

        myCommand.CommandText = "UPDATE T_ParametryValue SET real_value = @hodnota FROM T_ParametryValue INNER JOIN" _
                  & " T_Parametry ON T_ParametryValue.ID_parametr = T_Parametry.ID_parametr" _
                  & " WHERE (rtrim(T_Parametry.Nazev) = rtrim(@PHE))"
        myCommand.CommandType = CommandType.Text

        myCommand.Parameters.AddWithValue("@hodnota", hodnota)
        myCommand.Parameters.AddWithValue("@PHE", PHE)

        retVal = myCommand.ExecuteNonQuery()
        dbConnection.Close()
        Return retVal
    End Function

    Public Function PHEsDPH(ByVal ID_objCislo As String, Optional ByVal EUR As String = "") As Single
        Dim cenasPoplatkem As Single
        Dim PHE711 As Boolean
        Dim PHE715 As Boolean
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("DataHorejsi").ConnectionString
        Dim dbConnection As System.Data.SqlClient.SqlConnection = New SqlConnection(connectionString)
        Dim myCommand As SqlCommand = New SqlCommand
        Dim mydataSet As New DataSet
        Dim dataAdapter As SqlDataAdapter
        Dim myRow As DataRow


        dbConnection.Open()
        myCommand.Connection = dbConnection


        myCommand.CommandText = String.Format("SELECT ID_objcislo, DPH, PHE711, PHE715" _
                                              & " FROM  T_Produkt where ID_objcislo=rtrim('{0}')", ID_objCislo)
        myCommand.CommandType = CommandType.Text

        dataAdapter = New SqlDataAdapter(myCommand.CommandText, dbConnection)

        dataAdapter.Fill(mydataSet, "Poplatky")


        myRow = mydataSet.Tables("Poplatky").Rows(0)

        cenasPoplatkem = 0
        PHE711 = CType(myRow("PHE711"), Boolean)
        PHE715 = CType(myRow("PHE715"), Boolean)

        If PHE711 Then
            cenasPoplatkem = (nactiUlozenyPoplatek("PHE711") * (1 + myRow("DPH") / 100.0))
        End If

        If PHE715 Then
            cenasPoplatkem = (nactiUlozenyPoplatek("PHE715") * (1 + myRow("DPH") / 100.0))
        End If

        dbConnection.Close()

        If EUR <> "" Then
            Dim kurz As New KurzCNB
            cenasPoplatkem = cenasPoplatkem / kurz.nactiUlozenyKurz("EUR")
        End If

        Return cenasPoplatkem
    End Function
End Class
